package com.xjj.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.xjj.entity.Manager;
import com.xjj.entity.School;
import com.xjj.entity.Student;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;
import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.annotations.*;
import org.apache.ibatis.jdbc.SQL;
import org.springframework.util.StringUtils;

import java.util.List;

/**
 * @author sxy
 * @version 1.0
 * @date 2022/10/20 12:20
 */
@Mapper
public interface StudentMapper extends BaseMapper<Student> {
    @Update("update xjj_student set sid=#{sid}, name=#{name}, gender=#{gender}, birthday=#{birthday}, tel=#{tel}, late_num=#{lateNum}, ask_leave=#{askLeave}, submitted_num=#{submittedNum} where id=#{id}")
    void updateStudentById(Student student);

    /**
     * 多条件查询学生
     *
     * @param student
     * @return 根据指定条件动态查询到的用户信息的 List 集合
     */
    @SelectProvider(type = StudentSqlProvider.class, method = "select")
    @Results(id = "studentMap", value = {
            @Result(id = true, column = "id", property = "id"),
            @Result(column = "sid", property = "sid"),
            @Result(column = "name", property = "name"),
            @Result(column = "gender", property = "gender"),
            @Result(column = "birthday", property = "birthday"),
            @Result(column = "tel", property = "tel"),
            @Result(column = "klass_id", property = "klassId"),
            @Result(column = "school_id", property = "schoolId"),
            @Result(column = "parent_id", property = "parentId"),
            @Result(column = "late_num", property = "lateNum"),
            @Result(column = "absence_num", property = "absenceNum"),
            @Result(column = "ask_leave", property = "askLeave"),
            @Result(column = "submitted_num", property = "submittedNum"),
            @Result(column = "attend_status", property = "attendStatus")
    })
    List<Student> getByCondition(Student student);

    @UpdateProvider(type = StudentSqlProvider.class, method = "update")
    void updateByCondition(Student student);

    @Select("select id,name from xjj_student where klass_id = #{klassId}")
    List<Student> getAllStudentsByKlassId(int klassId);

    @Select("select s.name from xjj_school s , xjj_student u where u.school_id=#{schoolId}")
    School getSchoolBySchoolId(int schoolId);

    @Select("select * from xjj_student where klass_id = #{klassId}")
    List<Student> getAllStudentsByKlassId1(int klassId);

    @Select("SELECT t.*,s.name as schoolName,k.name as klassName,p.name as parentName FROM xjj_school s,xjj_student t,xjj_klass k,xjj_parent p WHERE t.school_id=s.id AND t.klass_id=k.id AND t.parent_id=p.id")
    List<Student> findAllStudent();

    /**
     * 根据家长手机号查询学生信息
     *
     * @param tel 家长手机号
     * @return Student 对象 集合
     */
    @Select("SELECT t.* , s.name as schoolName, k.name as klassName, p.name as parentName " +
            "FROM xjj_student t " +
            "LEFT JOIN xjj_school s ON t.school_id=s.id " +
            "LEFT JOIN xjj_klass k ON t.klass_id=k.id  " +
            "LEFT JOIN xjj_parent p ON t.parent_id=p.id " +
            "WHERE t.tel=#{tel}")
    List<Student> getByTel(String tel);

    @Update("update xjj_student set submitted_num = submitted_num+1 where id = #{id}")
    void addSubmitNumById(int id);

    @Update("update xjj_student set submitted_num = submitted_num-1 where id = #{id}")
    void decreaseSubmitNumById(int id);

    @Slf4j
    class StudentSqlProvider {
        /**
         * 动态更新sql语句
         *
         * @param student
         * @return
         */
        public String update(Student student) {
            return new SQL() {
                {
                    UPDATE("xjj_student");
                    if (StringUtils.hasLength(student.getSid())) {
                        SET("sid=#{sid}");
                    }
                    if (StringUtils.hasLength(student.getName())) {
                        SET("name=#{name}");
                    }
                    if (StringUtils.hasLength(student.getGender())) {
                        SET("gender=#{gender}");
                    }
                    if (StringUtils.hasLength(student.getBirthday())) {
                        SET("birthday=#{birthday}");
                    }
                    if (StringUtils.hasLength(student.getTel())) {
                        SET("tel=#{tel}");
                    }
                    if (student.getKlassId() != null) {
                        SET("klass_id=#{klassId}");
                    }
                    if (student.getSchoolId() != null) {
                        SET("school_id=#{schoolId}");
                    }
                    if (student.getParentId() != null) {
                        SET("parent_id=#{parentId}");
                    }
                    if (student.getLateNum() != null) {
                        SET("late_num=#{lateNum}");
                    }
                    if (student.getAbsenceNum() != null) {
                        SET("absence_num=#{absenceNum}");
                    }
                    if (student.getAskLeave() != null) {
                        SET("ask_leave=#{askLeave}");
                    }
                    if (student.getSubmittedNum() != null) {
                        SET("submitted_num=#{submittedNum}");
                    }
                    if (StringUtils.hasLength(student.getAttendStatus())) {
                        SET("attend_status=#{attendStatus}");
                    }
                    WHERE("id=#{id}");
                }
            }.toString();
        }

        /**
         * 得到动态产生的多条件查询 sql 语句
         *
         * @param student
         * @return 动态产生的 sql 查询语句
         */
        public String select(Student student) {
            return new SQL() {
                {
                    SELECT("*").FROM("xjj_student");
                    if (StringUtils.hasLength(student.getSid())) {
                        WHERE("sid=#{sid}");
                    }
                    if (StringUtils.hasLength(student.getName())) {
                        WHERE("name=#{name}");
                    }
                    if (StringUtils.hasLength(student.getGender())) {
                        WHERE("gender=#{gender}");
                    }
                    if (StringUtils.hasLength(student.getBirthday())) {
                        WHERE("birthday=#{birthday}");
                    }
                    if (StringUtils.hasLength(student.getTel())) {
                        WHERE("tel=#{tel}");
                    }
                    if (student.getKlassId() != null) {
                        WHERE("klass_id=#{klassId}");
                    }
                    if (student.getSchoolId() != null) {
                        WHERE("school_id=#{schoolId}");
                    }
                    if (student.getParentId() != null) {
                        WHERE("parent_id=#{parentId}");
                    }
                    if (student.getLateNum() != null) {
                        WHERE("late_num=#{lateNum}");
                    }
                    if (student.getAbsenceNum() != null) {
                        WHERE("absence_num=#{absenceNum}");
                    }
                    if (student.getAskLeave() != null) {
                        WHERE("ask_leave=#{askLeave}");
                    }
                    if (student.getSubmittedNum() != null) {
                        WHERE("submitted_num=#{submittedNum}");
                    }
                    if (StringUtils.hasLength(student.getAttendStatus())) {
                        WHERE("attend_status=#{attendStatus}");
                    }
                }
            }.toString();
        }
    }
}
